New types of data and new data science technologies enable new research. These new technologies are technologies such as the ability to combine existing data or the ability to generate synthetic data from existing knowledge. This week casus is based on such research. Data is generated by Synthea's COVID-19 module. The data was constructed using three peer-reviewed publications published in the early stages of the global pandemic, when less was known, along with emerging resources, data, publications, and clinical knowledge. The simulation outputs synthetic Electronic Health Records (EHR), including the daily consumption of Personal Protective Equipment (PPE) and other medical devices and supplies. The Data is stored in separate tables to avoid redundancy, with as a concequence that tables need to be combined and reorganized in dataframes for analysing purpose.
Keywords: merge data, subset data, clean data, generate data
You will learn about combining data with pandas and numpy and you will learn to visualize with bokeh. Concretely, you will preprocess the partly Synthetic Covid data in an appropiate format in order to conduct statistical and visual analysis. Learning objectives
Tutorials about combining data: https://github.com/fenna/BFVM22PROG1/blob/main/tutorials/tutorial_combine_data.ipynb
study case combining data:https://github.com/fenna/BFVM22PROG1/blob/main/study_cases/adults_who_binge_drank_in_hot_towns.ipynb
Please add the topics you want to learn about here: https://padlet.com/ffeenstra1/kzh2chaqleq3iovu
Your job is to visualize the lab values taken for COVID-19 patients of survived versus not survived patients.
The assignment consists of 6 parts:
Part 1 and 4 are mandatory, part 5 is optional (bonus) Mind you that you cannot copy code without referencing the code. If you copy code you need to be able to explain your code verbally and you will not get the full score.
The data is generated by Synthea's COVID-19 module. The data was constructed using three peer-reviewed publications published in the early stages of the global pandemic, when less was known, along with emerging resources, data, publications, and clinical knowledge. The simulation outputs synthetic Electronic Health Records (EHR), including the daily consumption of Personal Protective Equipment (PPE) and other medical devices and supplies. For this assignment the conditions, patients, observations, careplans and encounters table will be used. The Data is stored in separate tables to avoid redundancy, with as a concequence that tables need to be combined and reorganized in dataframes for analysing purpose.
Source: Walonoski J, Klaus S, Granger E, Hall D, Gregorowicz A, Neyarapally G, Watson A, Eastman J. Synthea™ Novel coronavirus (COVID-19) model and synthetic data set. Intelligence-Based Medicine. 2020 Nov;1:100007. https://doi.org/10.1016/j.ibmed.2020.100007
Please download the data
Patients are considered Covid patients if they are identified with CODE 840539006
Patients that had covid and where tested negative after isolation have tested code 94531-1, SARS-CoV-2 RNA Pnl Resp NAA+probe (covid-sars test) + a value of Not detected (qualifier value). These patients are considered to be survived covid patients.
Patients that did not survived Covid have a DEATHDATE which is not null.
Patients are monitored for blood and heart conditions once they are admitted in Hospital or under treatment. The lab values of interest are as follow:
48065-7 Fibrin D-dimer FEU [Mass/volume] in Platelet poor plasma26881-3 Interleukin 6 [Mass/volume] in Serum or Plasma2276-4 Ferritin [Mass/volume] in Serum or Plasma89579-7 Troponin I.cardiac [Mass/volume] in Serum or Plasma by High sensitivity method731-0 Lymphocytes [#/volume] in Blood by Automated count14804-9 Lactate dehydrogenase [Enzymatic activity/volume] in Serum or Plasma by Lactate to pyruvate reactionInstructions: Load the data of the following files. Preferably we read the data not with a hard coded data path but using a config file. See https://fennaf.gitbook.io/bfvm22prog1/data-processing/configuration-files/yaml
Get yourself familiar with the data. Create some meaningful overviews. Answer the following questions
import yaml
import pandas as pd
import numpy as np
files = ['conditions.csv', 'patients.csv', 'observations.csv', 'careplans.csv', 'encounters.csv']
with open("config.yaml", 'r') as config:
data3 = yaml.safe_load(config)['data3']
df_dict = {
'conditions': pd.read_csv(data3 + files[0]),
'patients': pd.read_csv(data3 + files[1]),
'observations': pd.read_csv(data3 + files[2]),
'careplans': pd.read_csv(data3 + files[3]),
'encounters': pd.read_csv(data3 + files[4])
}
num_patients = df_dict["patients"]
covid_patients = df_dict["conditions"][df_dict["conditions"]["CODE"] == 840539006]
num_admitted = df_dict["observations"][df_dict["observations"]['CODE'].isin(['48065-7', '26881-3', '2276-4','89579-7', '731-0', '14804-9'])]['PATIENT'].unique()
num_died = df_dict["patients"][df_dict["patients"]['DEATHDATE'].notnull()]
num_patients
| Id | BIRTHDATE | DEATHDATE | SSN | DRIVERS | PASSPORT | PREFIX | FIRST | LAST | SUFFIX | ... | BIRTHPLACE | ADDRESS | CITY | STATE | COUNTY | ZIP | LAT | LON | HEALTHCARE_EXPENSES | HEALTHCARE_COVERAGE | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | f0f3bc8d-ef38-49ce-a2bd-dfdda982b271 | 2017-08-24 | NaN | 999-68-6630 | NaN | NaN | NaN | Jacinto644 | Kris249 | NaN | ... | Beverly Massachusetts US | 888 Hickle Ferry Suite 38 | Springfield | Massachusetts | Hampden County | 1106.0 | 42.151961 | -72.598959 | 8446.49 | 1499.08 |
| 1 | 067318a4-db8f-447f-8b6e-f2f61e9baaa5 | 2016-08-01 | NaN | 999-15-5895 | NaN | NaN | NaN | Alva958 | Krajcik437 | NaN | ... | Boston Massachusetts US | 1048 Skiles Trailer | Walpole | Massachusetts | Norfolk County | 2081.0 | 42.177370 | -71.281353 | 89893.40 | 1845.72 |
| 2 | ae9efba3-ddc4-43f9-a781-f72019388548 | 1992-06-30 | NaN | 999-27-3385 | S99971451 | X53218815X | Mr. | Jayson808 | Fadel536 | NaN | ... | Springfield Massachusetts US | 1056 Harris Lane Suite 70 | Chicopee | Massachusetts | Hampden County | 1020.0 | 42.181642 | -72.608842 | 577445.86 | 3528.84 |
| 3 | 199c586f-af16-4091-9998-ee4cfc02ee7a | 2004-01-09 | NaN | 999-73-2461 | S99956432 | NaN | NaN | Jimmie93 | Harris789 | NaN | ... | Worcester Massachusetts US | 201 Mitchell Lodge Unit 67 | Pembroke | Massachusetts | Plymouth County | NaN | 42.075292 | -70.757035 | 336701.72 | 2705.64 |
| 4 | 353016ea-a0ff-4154-85bb-1cf8b6cedf20 | 1996-11-15 | NaN | 999-60-7372 | S99917327 | X58903159X | Mr. | Gregorio366 | Auer97 | NaN | ... | Patras Achaea GR | 1050 Lindgren Extension Apt 38 | Boston | Massachusetts | Suffolk County | 2135.0 | 42.352434 | -71.028610 | 484076.34 | 3043.04 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 12347 | eec72396-d10e-4b8e-8fb6-25fc1e396031 | 1962-08-17 | NaN | 999-52-5112 | S99981543 | X65879689X | Mrs. | Althea11 | O'Hara248 | NaN | ... | Delhi Delhi IN | 682 Koss Trafficway Apt 65 | Wellesley | Massachusetts | Norfolk County | 2457.0 | 42.294175 | -71.259364 | 1332256.08 | 7157.90 |
| 12348 | 3daf2b90-c439-469f-bb44-fb6109c6a8a7 | 1918-10-20 | 1999-11-01 | 999-25-1153 | S99951005 | X1374154X | Mrs. | Tarah156 | Shields502 | NaN | ... | New Bedford Massachusetts US | 308 Huels Grove Apt 18 | Waltham | Massachusetts | Middlesex County | 2453.0 | 42.372663 | -71.209053 | 1557234.62 | 27214.48 |
| 12349 | cb03d88e-7f08-46a1-be78-70d9fe8262a7 | 1918-10-20 | 2009-08-07 | 999-41-7538 | S99938113 | X29702266X | Mrs. | Penny812 | Pacocha935 | NaN | ... | Marshfield Massachusetts US | 349 Breitenberg Walk Suite 26 | Waltham | Massachusetts | Middlesex County | 2452.0 | 42.396186 | -71.217928 | 1347930.08 | 26001.41 |
| 12350 | 435be43f-cb57-442f-aeb2-51018b030ed0 | 1918-10-20 | NaN | 999-26-4571 | S99995011 | X51104300X | Ms. | Cherlyn665 | Quitzon246 | NaN | ... | Holyoke Massachusetts US | 237 Miller Avenue | Waltham | Massachusetts | Middlesex County | 2452.0 | 42.366768 | -71.196715 | 1585631.29 | 32006.97 |
| 12351 | 2712205f-755e-4897-acb3-926895b7d635 | 1941-02-11 | NaN | 999-57-4019 | S99932976 | X25566175X | Mr. | Emory494 | Turcotte120 | NaN | ... | Medford Massachusetts US | 595 Zulauf Parade Suite 56 | Bourne | Massachusetts | Barnstable County | 2532.0 | 41.721661 | -70.587214 | 1689239.58 | 790451.35 |
12352 rows × 25 columns
The following function needs to be called. You can use this as a test. There are however more meaningful overviews you can create.
def part1(num_pat, num_cov, num_admitted, num_died):
print(f'There are {num_pat} patients in total')
print(f'There are {num_cov} covid patients')
print(f'There are {num_admitted} admitted patients')
print(f'{num_died} patients died')
part1(len(num_patients), len(covid_patients), len(num_admitted), len(num_died))
There are 12352 patients in total There are 8820 covid patients There are 1867 admitted patients 2352 patients died
In this part we are going to combine data to create a dataframe with values of interest for the lab values analysis.
We would like a dataframe containing the following information per record (only Covid patients!!!)
PATIENT - the ID of the covid patientdays - the number of days the patient is under observationCODE-Y - the code of the observationVALUE - the lab value of the observationwhere only the following observation codes needs to be selected:
48065-7 Fibrin D-dimer FEU [Mass/volume] in Platelet poor plasma26881-3 Interleukin 6 [Mass/volume] in Serum or Plasma2276-4 Ferritin [Mass/volume] in Serum or Plasma89579-7 Troponin I.cardiac [Mass/volume] in Serum or Plasma by High sensitivity method731-0 Lymphocytes [#/volume] in Blood by Automated count14804-9 Lactate dehydrogenase [Enzymatic activity/volume] in Serum or Plasma by Lactate to pyruvate reactionThe days information is not primarely available and needs to be calculated by substracting observation DATE - START.
An example of such a dataframe is given below:
#Possible approach:
#Select all the patients with covid from the conditions table
#Combine conditions table (only covid patients) with the patient table into a covid_patient table
#select the only the relevant lab observations from the observations table into a lab_obs table
#merge the covid_patient table with the lab_obs table into a covid_patients_obs table
#clean the covid_patients_obs table (rename columns, select only relevant columns, sort, typecast, add days column)
#YOUR CODE HERE
covid = df_dict["conditions"][df_dict["conditions"]["CODE"] == 840539006]
patients = df_dict["patients"]
observations = df_dict["observations"][df_dict["observations"]['CODE'].isin(['48065-7', '26881-3', '2276-4','89579-7', '731-0', '14804-9'])]
lab_obs = pd.merge(observations, covid, on='PATIENT', how="inner")
lab_obs['DAYS'] = (pd.to_datetime(lab_obs['DATE']) - pd.to_datetime(lab_obs['START'])).astype("string").str[0:2].astype("int")
clean_lab_obs = lab_obs[["PATIENT", "DAYS", "CODE_x", "VALUE", "UNITS"]]
clean_lab_obs['VALUE'] = pd.to_numeric(clean_lab_obs['VALUE'])
clean_lab_obs['DAYS'].max()
/tmp/ipykernel_253553/3619464563.py:16: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy clean_lab_obs['VALUE'] = pd.to_numeric(clean_lab_obs['VALUE'])
26
Now we have the required data we would like to split the data into survived and not survived. First we fetch all the ids of the survived and deceased patients. We can use these ids to select the records of the survived patients and the patients that did not survived
Your job is to split the data into survived and not survived records. There are multiple ways to do this. One way is the .isin() method
#the following code is given, RUN THIS CELL
#get survived and deceased ids
care_plans = df_dict['careplans']
observations = df_dict['observations']
patients = df_dict['patients']
completed_isolation_patients = care_plans[(care_plans.CODE == 736376001) & (care_plans.STOP.notna()) \
& (care_plans.REASONCODE == 840539006)].PATIENT
negative_covid_patient_ids = observations[(observations.CODE == '94531-1') \
& (observations.VALUE == 'Not detected (qualifier value)')].PATIENT.unique()
survivor_ids = np.union1d(completed_isolation_patients, negative_covid_patient_ids)
deceased_ids = patients[patients.DEATHDATE.notna()].Id
#YOUR CODE HERE
survived = clean_lab_obs[clean_lab_obs["PATIENT"].isin(survivor_ids)]
dead = clean_lab_obs[clean_lab_obs["PATIENT"].isin(deceased_ids)]
def test3(survived, died):
print(f'patients records survived: {survived}, patients records deceased {died}')
#call the test3
test3(len(survived), len(dead))
patients records survived: 57303, patients records deceased 16793
Create plots with the lab data, for each code one plot. Separate the survivors and the deceased by color. An example of such a plot is given below. You can create 6 plots in one grid (for each code one plot) or use a widget (for instance a drop down menu widget) to select a lab CODE. Plot on the x-axis the days, on the y-axis the VALUE. Use proper labels, titles and legends.
#YOUR CODE HERE
from bokeh.io import output_notebook
from bokeh.plotting import figure, show
from bokeh.layouts import gridplot
from bokeh.tile_providers import CARTODBPOSITRON, get_provider
output_notebook()
def make_plot(code, title):
surv = survived[survived["CODE_x"] == code]
death = dead[dead["CODE_x"] == code]
p = figure(title = title)
p.circle(x=surv["DAYS"], y=surv["VALUE"], color = "green", legend_label='survived')
p.circle(x=death["DAYS"], y=death["VALUE"], color = "red", legend_label='died')
p.legend.location = "top_left"
p.legend.title = "observations"
return p
def create_plots():
plots = []
observations = {
'48065-7': 'Fibrin D-dimer FEU [Mass/volume] in Platelet poor plasma',
'26881-3': 'Interleukin 6 [Mass/volume] in Serum or Plasma',
'2276-4': 'Ferritin [Mass/volume] in Serum or Plasma',
'89579-7': 'Troponin I.cardiac [Mass/volume] in Serum or Plasma by High sensitivity method',
'731-0': 'Lymphocytes [/volume] in Blood by Automated count',
'14804-9': 'Lactate dehydrogenase [Enzymatic activity/volume] in Serum or Plasma by Lactate to pyruvate reaction'
}
for code, title in observations.items():
plots.append(make_plot(code, title))
return plots
graphs = create_plots()
show(gridplot(graphs, ncols=2, width=450, height=300))
This is a bonus part. Can you plot the patients location on a map? See also https://docs.bokeh.org/en/latest/docs/user_guide/geo.html
You can use either package folium or geopandas. You need the Latitude and Longitude information from the patient tabel
# Define function to switch from lat/long to mercator coordinates
def coordinates(x, y):
lat = x
lon = y
r_major = 6378137.000
x = r_major * np.radians(lon)
scale = x/lon
y = 180.0/np.pi * np.log(np.tan(np.pi/4.0 +
lat * (np.pi/180.0)/2.0)) * scale
return (x, y)
# Define coord as tuple (lat,long)
df_dict['patients']['coordinates'] = list(zip(df_dict['patients']['LAT'], df_dict['patients']['LON']))
# Obtain list of mercator coordinates
mercators = [coordinates(x, y) for x, y in df_dict['patients']['coordinates'] ]
# Create mercator column in our df
df_dict['patients']['mercator'] = mercators
# Split that column out into two separate columns - mercator_x and mercator_y
df_dict['patients'][['mercator_x', 'mercator_y']] = df_dict['patients']['mercator'].apply(pd.Series)
source = df_dict['patients']
#create figure
p = figure(x_axis_type="mercator", y_axis_type="mercator", x_axis_label = 'Longitude', y_axis_label = 'Latitude')
#add map
tile_provider = get_provider(CARTODBPOSITRON)
p.add_tile(tile_provider)
p.circle(x = 'mercator_x', y = 'mercator_y', source=source, size=3, fill_alpha = 0.7)
show(p)